package jwanalytics.dbloader;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.StringTokenizer;
import java.util.Vector;

import org.apache.log4j.Logger;

import com.mchange.v2.c3p0.*; //for connection pooling.

/** 
 * DB represents the database access and offers methods such as: getConnection().
 * Read the JWAnalytics manual on how to add this loader to your own existing 
 * application and make it use your own connection pool 
 * class and database.  This will involve subclassing this class (DB) and overriding 
 * its methods.
 * 
 * You also might want to have a separate database for your web analytics (other than your
 * application business data database) in which case this will work just fine with this class
 * and its properties obtained from jwanalytics.properties file.
 */

public class DB {
	
	/** C3PO connection pooled data source. */
	public static ComboPooledDataSource cpds = new ComboPooledDataSource(); 

	public static Logger logger = Logger.getLogger("jwanalytics");
	
	static {
		Props.init(Props.PROPS_FILE, false);
		try {
			cpds.setDriverClass(Props.get(Props.PROPS_FILE, "jdbcdriver"));
		} catch (PropertyVetoException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//loads the jdbc driver 
		cpds.setJdbcUrl(Props.get(Props.PROPS_FILE, "jdbcurl"));
		cpds.setMaxStatements(50);
		cpds.setUser(Props.get(Props.PROPS_FILE, "jdbcuser")); 
		cpds.setPassword(Props.get(Props.PROPS_FILE, "test")); 
	}
	
	
	/** 
	 * Gets a connection from the connection pool  Override to use your own connection pool. 
	 * @throws SQLException */
	public static Connection getConnection() throws SQLException {
		return DB.cpds.getConnection();		
	}
	
	
	/** This will create any table, drop any table, etc. 
	 * @param statement SQL statement to be executed against database.
	 */
	public static boolean executeStatement(String statement) throws Exception {
		Connection con = null;
		PreparedStatement st = null;
		boolean toReturn = false;
		try {
			con  = DB.getConnection();
			st = con.prepareStatement(statement);
			logger.debug("Executing:"+statement);
			toReturn = st.execute();
			if (toReturn == false) {
				//This proves important to assist in porting to other databases.
				//It happens that for create table and drop table the execute will fail without an exception.
				//Below logged as info, and not error as drop tables could be executed without a table in which
				//case the statement will fail.
				if (st.getWarnings() != null) {
					logger.warn("WAINFO0003:"+st.getWarnings().toString());
				}
			}
		} catch (Exception e) {
			logger.warn("Error on statement:"+statement);
			throw e;
		} finally {
			DB.close(con, st, null);
		}		
		return toReturn;
	}	
	
	
	/** 
	 * Just returns the value of a database sequence.
	 * Normally this is optimized by databased (eg. postgres) which cache sequence values in memory from 1 (enough mostly) to multiple.
	 * This is obtained separately for reasons of compatibility with multiple databases (some cannot obtain sequence and insert in one single statement).  
	 * @throws SQLException */
	public static int getSequenceValue(String sequence_name) throws SQLException {
		Connection con = DB.getConnection();
		int seq = getSequenceValue(con, sequence_name);
		close(con, null, null);
		return seq;
	}
	
	
	/** 
	 * Just returns the value of a database sequence.
	 * Normally this is optimized by databased (eg. postgres) which cache sequence values in memory from 1 (enough mostly) to multiple.
	 * This is obtained separately for reasons of compatibility with multiple databases (some cannot obtain sequence and insert in one single statement).  
	 * @throws SQLException */
	public static int getSequenceValue(Connection con, String sequence_name) throws SQLException {
		String sqlStr = "SELECT nextval('"+sequence_name+"')";
		Statement st = null;
		ResultSet s = null;
		int toReturn = -1;
		try {
			st = con.createStatement();
			st.executeQuery(sqlStr);
			s = st.getResultSet();
			if (s.next()) {
				toReturn = s.getInt(1); //only one column.
				//logger.debug("getSequenceValue("+sequence_name+"):"+toReturn);
			} else {
				logger.error("No next value for sequence:"+sequence_name);
			}
		} catch (Exception e) {
			//@TODO: use a logger.
			logger.error("WAERROR0004: could not obtain sequence value for sequence:"+sequence_name+", Exception:"+e);
		} finally {
			//do not close connection as it is being passed.
			if (s != null) s.close();
			if (st != null) st.close();
			return toReturn;
		}		
	}
	
	/** 
	 * Drop table or sequence to be used by CreateDatabase or any client willing to ignore exceptions.  
	 * Will propagate exceptions for when table does not exist or referencial integrity prevents dropping.
	 * 
	 * This method just calls executeStatement.
	 * 
	 * @throws Exception 
	 * @see RecreateDatabase
	 * @see executeStatement
	 */
	public static void dropStatement(String ddl) throws Exception {
		DB.executeStatement(ddl); 
	}
	
	
	/** 
	 * This is a handy method to close the database connection, prepared statement, result set all in one line.
	 */
	public static void close(Connection con, Statement st, ResultSet rs) {
		try {
			if (rs != null) rs.close();
			if (st != null) st.close();
			if (con != null) { con.close(); }
		} catch (Exception e) {
			//@TODO: use a logger.
			System.err.println("WAINFO0002:Could not close database connection.");
		}
	}
	
	
	/** 
	 * This method generates a complete SQL insert statement for a table and a set of properties.
	 * See unit test for more details.  This method is used for automatic persistance of data objects
	 * for dimension and fact table rows.
	 * 
	 * @param tableName The name of the table to generate the insert for.
	 * @param pAllPropertyNames The list of property names to be used in the insert. Values are bound.
	 */
	public static StringBuilder generateInsertStatement(String tableName, String[] pAllPropertyNames) {
		StringBuilder stmt = new StringBuilder("INSERT INTO ").append(tableName).append(" (");
		StringBuilder stmtPart2 = new StringBuilder(""); //This is the middle of the VALUES() parenthesis of the insert statement.  Done in paralelal here for performance reasons.
		for (int i = 0; i < pAllPropertyNames.length; i++) {
			stmt.append(pAllPropertyNames[i]);
			stmtPart2.append("?");
			if (i < (pAllPropertyNames.length - 1) && pAllPropertyNames.length > 1) {
				stmt.append(", ");
				stmtPart2.append(", ");
			}
		}
		stmt.append(") VALUES(");
		stmt.append(stmtPart2);
		stmt.append(")");
		//
		//Run sql statement.
		//@TODO: use a logger.
		System.err.println("Statement:"+stmt.toString());
		return stmt;
	}
	
	
	/** Generates a full select SQL statement for a table, a list of all properties.
	 * The where clause will be generated using = and 'and' operators for the list of properties
	 * passed as parameters.
	 * 
	 *  See unit test for more details.
	 * @param tableName The name of the table to generate the select for.
	 * @param allPropertyNames The list of all property names allowed for this table.
	 * @param properties The list of property names intended for use in this select. 
	 * */
	public static StringBuilder generateSelectForFieldSubset(String tableName, String[] allPropertyNames, HashMap properties) { //reason why we do not generate only based on properties is that it could contain not persisted fields.	
		StringBuilder stmt = new StringBuilder("SELECT * FROM ").append(tableName).append(" WHERE ");
		int usedCount = 0;  //counts the number of properties used in the select.
		for (int i = 0; i < allPropertyNames.length; i++) {
			//logger.debug("Generating select: property["+i+"], value:"+allPropertyNames[i]);
			if (properties.containsKey(allPropertyNames[i])) {
				//logger.debug("Property contained.");
				stmt.append(allPropertyNames[i]).append("=").append("? ");
				++usedCount;
				//If not the last property and total number of properties to be used larger than one.
				if (usedCount < properties.size() && properties.size() > 1) {
					stmt.append(" and ");
				}
			}
		}
		return stmt;
	}
	
	/** Like the insert and select statement generators above, but now for updates where the whereFields are
	 * created with = operation and joined with 'and' operator. */
	public static StringBuilder generateUpdateStatement(String tableName, String[] fieldNames, String[] whereFields) {
		StringBuilder stmt = new StringBuilder("UPDATE ").append(tableName).append(" SET ");
		for (int i = 0; i < fieldNames.length; i++) {
			stmt.append(fieldNames[i]).append("=?");
			if (fieldNames.length > 1 && i < (fieldNames.length-1)) {
				stmt.append(", ");
			}
		}
		stmt.append(" WHERE ");
		for (int i = 0; i < whereFields.length; i++) {
			stmt.append(whereFields[i]).append("=?");
			if (whereFields.length > 1 && i < (whereFields.length-1)) {
				stmt.append(" and ");
			}
		}			
		//logger.debug("Update statement:"+stmt);
		return stmt;
	}
	
	
	/** Obtains the list of all table names from property all_tables_list on .properties file. */
	public static Vector<String> getAllTableNames(String file) throws Exception {
		return getPropertyList(file, "all_tables_list", ",");
	}
	
	/** Obtains the list of all table names from property all_tables_populated_during_runtime_list. */
	public static Vector<String> getAllRuntimePopulatedTableNames(String file) throws Exception {
		return getPropertyList(file, "all_tables_populated_during_runtime_list", ",");		
	}
	
	/**
	 * Mostly used to get a list of database tables to drop, etc.
	 * @param name name of the property to be obtained.
	 * @param separator separator of the property values (usually comma, ",").
	 * @return
	 */
	public static Vector<String> getPropertyList(String file, String name, String separator) throws Exception {
		String allTablesList = Props.get(file, name);
		if (allTablesList == null || allTablesList.length() == 0) throw new Exception("Table list not initialized on properties file.");
		StringTokenizer st = new StringTokenizer(allTablesList, separator);
		Vector<String> vAllTablesList = new Vector<String>();
		while (st.hasMoreTokens()) {
			vAllTablesList.add(st.nextToken().trim());
		}
		return vAllTablesList;		
	}
	
}
